使用excel vba更新Powerpoint中现有的嵌入式图表

2023-04-30 19:05| 来源: 网络整理| 查看: 265

百度翻译此文有道翻译此文问题描述

I have an inserted chart in powerpoint. I am using this as a template. I would want to edit the data of this chart with data from an excel sheet. Is there an excel vba code for this

推荐答案

Any Chart in PowerPoint (there are exceptions, and it is possible to "break" existing charts, but that's not in scope, here) has a ChartData property, which returns an Excel Workbook that contains the data for the chart.

When working from a "template" slide, it should be safe to assume that the chart data exists on Sheet 1, and in a ListObject table (there should be only one such table in the sheet).

In PowerPoint VBA, requiring reference to Excel object library, this shows you how to get a handle on the ListObject which contains the chart's data:

Sub ShowChartData() Dim sld As Slide Dim shp As Shape Dim cht As Chart Dim chtData As ChartData Dim cTable As Excel.ListObject 'Assume we have only one slide, at slide 1: Set sld = ActivePresentation.Slides(1) 'Assume the Chart is the second shape, modify if needed Set shp = sld.Shapes(2) 'Handle the chart Set cht = shp.Chart 'Handle the CharttData Set chtData = cht.ChartData 'Open & minimize the ChartData, you don't need to see it, but it must be OPEN to edit it chtData.Activate chtData.Workbook.Application.WindowState = -4140 With chtData Set cTable = chtData.Workbook.Worksheets(1).ListObjects(1) ' Here, you can update the ListObject in the same ways you ' would do so in Excel, natively. End With 'Remember to close the workbook chtData.Workbook.Close End Sub

Now that you have a handle on the ListObject, you need to somehow get the values from Excel.

This will require handling an open instance of Excel.Application class (or prompting the user to select a file from a FileDialog, etc.) and identifying which data to put in the PowerPoint, and how to arrange it. Usually this can be done by dumping the values from Excel in to a variant array, and passing that to PowerPoint.

Since those are all details you've omitted, please note that I am absolutely not willing to entertain what is likely to be a never-ending series of "but how do I do such-and-such...?" follow-up questions as you suss out the complexities of your own logic and use-case requirements.

The above code is designed to execute from PowerPoint. If you need to run it from Excel, it will require different code (untested, but something like this).

Sub ShowPPTChartData() ' to be run from Excel VBA 'Requires reference to PowerPoint library Dim ppt as PowerPoint.Application Dim pres as PowerPoint.Presentation Dim sld As PowerPoint.Slide Dim shp As PowerPoint.Shape Dim cht As PowerPoint.Chart Dim chtData As PowerPoint.ChartData Dim cTable As Excel.ListObject Set ppt = GetObject(,"PowerPoint.Application") 'Assume we have only one open Presentation file: Set pres = ppt.Presentations(1) 'Assume we have only one slide, at slide 1: Set sld = pres.Slides(1) 'Assume the Chart is the second shape, modify if needed Set shp = sld.Shapes(2) 'Handle the chart Set cht = shp.Chart 'Handle the CharttData Set chtData = cht.ChartData 'Open & minimize the ChartData, you don't need to see it, but it must be OPEN to edit it chtData.Activate chtData.Workbook.Application.WindowState = -4140 With chtData Set cTable = chtData.Workbook.Worksheets(1).ListObjects(1) ' Here, you can update the ListObject in the same ways you ' would do so in Excel, natively. End With 'Remember to close the workbook chtData.Workbook.Close End Sub

EDIT It is possible to edit an existing chart without Activating the ChartData.Workbook as can be demonstrated here:

Update PowerPoint chart without opening chart workbook or making it invisible

Adding/removing series from the charts is trickier than manipulating data that's already part of the chart series, however.

其他推荐答案Set Current_Chart_Shape = Active_Slide.Shapes("Monthly Chart") Set Current_Chart = Current_Chart_Shape.Chart Set Current_Chart_Data = Current_Chart.ChartData Current_Chart_Data.Activate Set PPTChartSheet = Current_Chart.ChartData.Workbook.Sheets(1) Current_Chart_Data.Workbook.Application.WindowState = -4140 With PPTChartSheet .ListObjects("Table1").Resize PPTChartSheet.Range("A1:C8") For l = 0 To 6 .Range("B2").Offset(l, 0).Value = Array_Values(l) Next l End With Current_Chart_Data.Workbook.Close 其他推荐答案

You don't need to do anything special.

Once a chart is embedded and linked to an Excel sheet, PowerPoint couldn't care less when or how it's updated, even when the PowerPoint file is closed. Basically, whatever you do to that sheet, VB or manually, will be reflected in the PowerPoint next time you open it.

【本文地址】

公司简介

联系我们